﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using Maticsoft.DBUtility;
using QunYing.Model;//Please add references
namespace QunYing.DAL
{
	/// <summary>
	/// 数据访问类:OrderInfo
	/// </summary>
	public partial class OrderInfo
	{
        ///// <summary>
        ///// 最大ID
        ///// </summary>
        ///// <returns></returns>
        //public static int GetMaxID()
        //{
        //    string strsql = "select ISNULL(max(订单ID),0)+1 FROM OrderInfo";
        //    return Convert.ToInt32(DbHelperSQL.GetSingle(strsql));
        //}
        /// <summary>
        /// 添加订单
        /// </summary>
        /// <param name="dt">商品</param>
        /// <returns></returns>
		public int AddOrder(string user,string opuser,string name,string address,string tel,string msg,DataTable dt)
        {
            decimal totalPrice = 0;
            int useScore = 0;
            int getScore = 0;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                totalPrice += Convert.ToDecimal(dt.Rows[i]["单价"]) * Convert.ToInt32(dt.Rows[i]["数量"]);
                useScore += Convert.ToInt32(dt.Rows[i]["消耗积分"]) * Convert.ToInt32(dt.Rows[i]["数量"]);
                getScore += Convert.ToInt32(dt.Rows[i]["赠送积分"]) * Convert.ToInt32(dt.Rows[i]["数量"]);
            }

            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into OrderInfo(");
            strSql.Append("订单编号,订单日期,总价,总积分,赠送总积分,用户ID,收件人,联系电话,收件地址,用户留言)");
            strSql.Append(" values ('");
            strSql.Append(DateTime.Now.ToString("yyMMddHHmmss") + new Random().Next(10000) + @"',
            getdate(),'" + totalPrice + @"','" + useScore + @"','" + getScore + @"','" + user + @"','" +name
                        + @"','" + tel + @"','" + address + @"','" + msg + @"'); ");
            strSql.Append("declare @i int; select @i= @@IDENTITY; ");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                strSql.Append("insert into OrderDetail(");
                strSql.Append("订单ID,商品ID,商品名称,商品品牌,单位,单价,积分,赠送积分,数量,类型)");
                strSql.Append(" values (");
                strSql.Append("@i,'" + dt.Rows[i]["商品ID"] + @"','" + dt.Rows[i]["商品名称"] + @"','" + dt.Rows[i]["品牌"] + @"','"
                    + dt.Rows[i]["单位"] + @"','" + dt.Rows[i]["单价"] + @"','" + dt.Rows[i]["消耗积分"] + @"','" + dt.Rows[i]["赠送积分"] + @"','" + dt.Rows[i]["数量"] + @"','" + dt.Rows[i]["类型"] + "'); ");
            }
            strSql.Append("insert into dbo.OrderChange(订单ID,操作人,订单状态,操作时间) values(@i,'" + opuser + "','" + Enums.Start + "',getdate()); ");
            strSql.Append("update Score set 积分=积分-" + useScore +"+"+ getScore);
            strSql.Append(" select @i; ");
           
            int res=Convert.ToInt32(DbHelperSQL.ExecuteSqlTran(strSql.ToString()));
            if (res > 0)
            {
                return res;
            }
            else
            {
                return 0;
            }
        }

       /// <summary>
       /// 我的订单
       /// </summary>
       /// <param name="user"></param>
       /// <returns></returns>
        public  DataTable GetOrderInfo(string user,bool b)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM (SELECT *,最后更新=(SELECT MAX(操作时间) FROM dbo.OrderChange b WHERE b.订单ID=a.订单ID),订单状态=(SELECT TOP 1 订单状态 FROM dbo.OrderChange b WHERE b.订单ID=a.订单ID ORDER BY b.操作时间 DESC) FROM dbo.OrderInfo a where a.用户ID=@用户ID)t ");
            strSql.Append(" WHERE t.订单状态 ");
            if (b)
           {
               strSql.Append(" NOT ");
           }
            strSql.Append("in('" + Enums.Canel + "','" + Enums.End + "','" + Enums.Error + "')");
           strSql.Append(" order by t.订单日期 DESC");
            SqlParameter[] parameters = {
					new SqlParameter("@用户ID",user)
             };
            return DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];
        }
        /// <summary>
        /// 经销商订单
        /// </summary>
        /// <param name="dealerID"></param>
        /// <returns></returns>
        public DataTable GetOrderList(string dealerID,string orderNo,DateTime? startDate,DateTime? endDate,string state,bool init)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"
            SELECT * FROM (SELECT a.*,c.经销商编号,最后更新=(SELECT MAX(操作时间) 
            FROM dbo.OrderChange b WHERE b.订单ID=a.订单ID),
            订单状态=(SELECT TOP 1 订单状态 FROM dbo.OrderChange b WHERE b.订单ID=a.订单ID ORDER BY b.操作时间 DESC) 
            FROM dbo.OrderInfo a
            LEFT JOIN dbo.VipInfo c ON a.用户ID=c.用户ID
            )t
            ");
            strSql.Append(" WHERE t.经销商编号='" + dealerID + "' ");
            strSql.Append(" and t.订单编号 like '%" + orderNo + "%' ");
            if (init)
            {
                strSql.Append(" and t.订单状态 in ('下单成功','正在备货','派送中') ");
            }
            else
            {
                strSql.Append(" and t.订单状态 like '%" + state + "%' ");
            }
            if (startDate==null&&endDate!=null)
            {
                strSql.Append(" and t.订单日期<='" + Convert.ToDateTime(endDate).ToString("yyyy-MM-dd HH:mm:ss") + "'");
            }
            if (endDate == null && startDate != null)
            {
                strSql.Append(" and t.订单日期>='" + Convert.ToDateTime(startDate).ToString("yyyy-MM-dd HH:mm:ss") + "'");
            }
            if (startDate != null && endDate != null)
            {
                strSql.Append(" and t.订单日期 between '" + Convert.ToDateTime(startDate).ToString("yyyy-MM-dd HH:mm:ss") + "' and '"+
                    Convert.ToDateTime(endDate).ToString("yyyy-MM-dd HH:mm:ss") + "'");
            }
            strSql.Append(" ORDER BY t.订单状态 DESC,t.最后更新 ASC ");
   
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }



        /// <summary>
        /// 销售统计
        /// </summary>
        /// <param name="DealerID"></param>
        /// <returns></returns>
        public DataTable QueryTotalMarket(string DealerID, DateTime startDate, DateTime endDate)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@经销商编号", DealerID),
                    new SqlParameter("@startDate", startDate),
                    new SqlParameter("@endDate", endDate)
              };
            return DbHelperSQL.RunProcedure("P_TotalMarket", parameters, "result").Tables[0];
        }
	}
}

